# coding=utf-8?
from docx import Document
from docx.shared import Inches
from docx.enum.style import WD_STYLE_TYPE
from docx.enum.section import WD_ORIENTATION
from docx.enum.style import WD_STYLE_TYPE
from docx.enum.table import WD_ROW_HEIGHT_RULE
from docx.enum.table import WD_TABLE_ALIGNMENT
from docx.enum.table import WD_CELL_VERTICAL_ALIGNMENT

# from docx.enum.text import WD_ALIGN_PARAGRAPH      #设置对象居中、对齐等。

from docx.oxml.ns import qn
from docx.shared import Inches, Cm, Pt  # 设置图像大小，设置像素、缩进等
from mailmerge import MailMerge

from docx.document import Document as _Document
from docx.oxml.text.paragraph import CT_P
from docx.oxml.table import CT_Tbl
from docx.table import _Cell, Table
from docx.text.paragraph import Paragraph

# import xlwings as xw
from openpyxl import load_workbook
import copy
import re

#from .courses import Courses
from excleapp.word.courses import Courses
#from .course import Course
#from .WordDBUse import DbUse
from excleapp.word.WordDBUse import DbUse


# 该类对excel表中的所有工作表的读取进行操作
class PeiYang():
    def __init__(self, root):
        self.courses = []
        self.row = 0
        self.col = 0
        self.tempPath = ""
        self.outPath = ""
        self.rootPath = ""

        self.periodPerCreditMajor = 24
        self.periodPerCreditCommon = 16
        self.proportions_credit = 0.0

        self.word_y = 0

        self.semester = [{}, {}, {}, {}, {}, {}, {}, {}, {}]  # 学期计数
        self.Course_proportions = {
            "人文社会科学类": {
                "credit":0.0,
                "period":0,
                "credit_proportion": 0.0
            },
            "自然科学类":  {
                "credit":0.0,
                "period":0,
                "credit_proportion": 0.0
            },
            "工程实践与毕业设计": {
                "credit":0.0,
                "period":0,
                "credit_proportion": 0.0
            },
            "工程基础类与专业类":  {
                "credit":0.0,
                "period":0,
                "credit_proportion": 0.0
            }
        }

        self.courseDict = {"通识课": None,
                           "通识选修课": None,
                           "学科基础课": None,
                           "学科专业基础课": None,
                           "专业选修课": None,
                           "集中性实践": None,
                           "第二课堂": None}

        self.width = [0.8, 1.8, 4, 1, 1.2, 1, 1, 1, 0.8, 0.8, 1.1, 1.4]
        self.rootPath = root
        self.userid = 0

    def setRoot(self, path):
        self.rootPath = path

    # 计算所有工作表的学分学时等
    def updateSummit(self):                # 计算学分学时
        self.period_all = 0  # 总学时
        self.credit_all = 0  # 总学分
        self.period_tech = 0  # 理论学时
        self.period_prac = 0  # 实践学时
        self.period_prac_in = 0  # 课内实践
        self.period_prac_out = 0  # 课外实践
        self.period_major_prac_in = 0 # 课内主修课实践
        self.period_major_prac_out = 0  # 课外主修课实践
        self.center_week_all = 0 # 周学时
        self.credit_could_major_choose_all = 0

        for s in self.semester:
            s['credit'] = 0
            s['period'] = 0
            s['period_in'] = 0
            s['kaoshi'] = []
            s['kaocha'] = []
            s['otherclass']=[]
            s['semester']= None

        for k, c in self.courseDict.items():
            c.updateSummit() # 计算单个表的学分学时等
            #c.Courseproportions()
            self.period_all += c.period_all
            self.credit_all += c.credit_all
            self.period_tech += c.period_tech
            self.period_prac_in += c.period_prac_in
            self.period_prac_out += c.period_prac_out
            self.period_prac += c.period_prac

            for z, data in self.Course_proportions.items():
                data['credit'] += c.Course_proportions[z]['credit']
                data['period'] += c.Course_proportions[z]['period']
            for i in range(len(self.semester)):
                self.semester[i]['credit'] += c.semester[i]['credit']
                self.semester[i]['period'] += c.semester[i]['period']
                self.semester[i]['period_in'] += c.semester[i]['period_in']
                self.semester[i]['otherclass'].extend(c.semester[i]['otherclass'])
                self.semester[i]['kaoshi'].extend(c.semester[i]['kaoshi'])
                self.semester[i]['kaocha'].extend(c.semester[i]['kaocha'])
                self.semester[i]['semester'] = i

        for z, data in self.Course_proportions.items():
            data['credit_proportion'] = (data['credit'] / (self.credit_all - self.courseDict['第二课堂'].credit_all))*100
            data['credit_proportion'] = round(data['credit_proportion'] , 1)# 保留小数点后一位
        # \续行符
        self.period_common_prac_in = self.courseDict["通识课"].period_prac_in \
                                     + self.courseDict["通识选修课"].period_prac_in
        self.period_common_prac_out = self.courseDict["通识课"].period_prac_out \
                                      + self.courseDict["通识选修课"].period_prac_out
        self.credit_common_prac_in = self.period_common_prac_in / self.periodPerCreditCommon
        self.credit_common_prac_out = self.period_common_prac_out / self.periodPerCreditCommon

        for s in ["学科基础课", "学科专业基础课", "专业选修课"]:
            self.period_major_prac_in += self.courseDict[s].period_prac_in
            self.period_major_prac_out += self.courseDict[s].period_prac_out
        self.credit_major_prac_in = self.period_major_prac_in / self.periodPerCreditMajor
        self.credit_major_prac_out = self.period_major_prac_out / self.periodPerCreditMajor

        self.center_week_all = self.courseDict["集中性实践"].center_week_all
        for number,date in enumerate(self.courseDict['专业选修课'].courses):
            self.credit_could_major_choose_all += date.dictData['学分数']
    def showSummit(self):
        print(self.period_all, "总学时")
        print(self.credit_all, "总学分")
        print(self.period_tech, "理论学时")
        print(self.period_prac, "实践学时")
        print(self.period_prac_in, "课内实践")
        print(self.period_prac_out, "课外实践")

        self.period_common_must = self.courseDict["通识课"].period_all
        self.credit_common_must = self.courseDict["通识课"].credit_all
        self.period_common_must_prac_out = self.courseDict["通识课"].period_prac_out

        self.period_common_choose = self.courseDict["通识选修课"].period_all
        self.credit_common_choose = self.courseDict["通识选修课"].credit_all
        self.credit_common_all = self.credit_common_must + self.credit_common_choose
        self.period_common_all = self.period_common_must + self.period_common_choose

        self.credit_common_p = self.credit_common_all / float(self.credit_all)
        self.period_common_p = self.period_common_all / float(self.period_all)

        self.period_basic_must = self.courseDict["学科基础课"].period_all
        self.credit_basic_must = self.courseDict["学科基础课"].credit_all
        self.period_basic_must_prac_out = self.courseDict["学科基础课"].period_prac_out
        # self.credit_basic_choose = self.courseDict["学科基础课选修课"].period_all
        # self.credit_basic_choose = self.courseDict["学科基础课选修课"].credit_all
        self.credit_basic_all = self.credit_basic_must
        self.period_basic_all = self.period_basic_must
        self.credit_basic_p = self.credit_basic_all / float(self.credit_all)
        self.period_basic_p = self.period_basic_all / float(self.period_all)

        self.period_major_must = self.courseDict["学科专业基础课"].period_all
        self.credit_major_must = self.courseDict["学科专业基础课"].credit_all
        self.period_major_must_prac_out = self.courseDict["学科专业基础课"].period_prac_out


        self.period_major_choose = self.courseDict["专业选修课"].period_all
        self.credit_major_choose = self.courseDict["专业选修课"].credit_all
        self.period_major_choose_prac_out = self.courseDict["专业选修课"].period_prac_out

        self.credit_major_all = self.credit_major_must + self.credit_major_choose
        self.period_major_all = self.period_major_must + self.period_major_choose
        self.credit_major_p = self.credit_major_all / float(self.credit_all)
        self.period_major_p = self.period_major_all / float(self.period_all)

        self.credit_major_not_choose=self.credit_could_major_choose_all - self.credit_major_choose
        self.credit_choose_p = (self.credit_common_choose + self.credit_could_major_choose_all) / (self.credit_all + (self.credit_could_major_choose_all-self.credit_major_choose))
        self.period_prac_p = ( self.period_prac +self.courseDict['学科基础课'].period_prac) / self.period_all

        print("通识必修课", self.credit_common_must, self.period_common_must)
        print("通识选修课", self.credit_common_choose, self.period_common_choose)
        print("学科基础课", self.credit_basic_must, self.period_basic_must)
        # print("学科基础课选修课", self.credit_basic_choose, self.period_basic_choose)
        print("学科专业基础课", self.credit_major_must, self.period_major_must)
        print("专业选修课", self.credit_major_choose, self.period_major_choose)

        for i in range(len(self.semester)):
            print(i, self.semester[i])

    def insertCell(self, cell, text, isCenter=True):
        if (not isinstance(text, str)):
            text = str(text)
        text = text.replace(".0", "")

        run = cell.paragraphs[0].add_run(text)  # 添加文本的方法
        run.font.name = u'宋体'
        run.font.size = Pt(9)  # 设置大小为10磅
        run._element.rPr.rFonts.set(qn('w:eastAsia'), u'宋体')
        # run.font.bold = True

        # 垂直对齐
        cell.vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.CENTER

        # 水平对齐
        if isCenter:
            cell.paragraphs[0].paragraph_format.alignment = WD_TABLE_ALIGNMENT.CENTER
        else:
            cell.paragraphs[0].paragraph_format.alignment = WD_TABLE_ALIGNMENT.LEFT

    def insertSectionTitle(self, document, text):
        p = document.add_paragraph()
        run = p.add_run(text)
        run.font.name = u'宋体'
        run.font.size = Pt(12)  # 设置大小为10磅
        run._element.rPr.rFonts.set(qn('w:eastAsia'), u'宋体')

    def delete_paragraph(self, paragraph):
        p = paragraph._element
        p.getparent().remove(p)
        # p._p = p._element = None
        paragraph._p = paragraph._element = None

    def clearWord(self, document):
        for p in range(len(document.paragraph)):
            document.paragraph[p].clear()

        for p in range(len(document.paragraph)):
            data = p.elements
            data.getparent().remove()
            p._p = p._element = None

    def iter_block_items(self, parent):
        """
        https://github.com/python-openxml/python-docx/issues/887
        Generate a reference to each paragraph and table child within *parent*,
        in document order. Each returned value is an instance of either Table or
        Paragraph. *parent* would most commonly be a reference to a main
        Document object, but also works for a _Cell object, which itself can
        contain paragraphs and tables.
        """
        if isinstance(parent, _Document):
            parent_elm = parent.element.body
            # print(parent_elm.xml)
        elif isinstance(parent, _Cell):
            parent_elm = parent._tc
        else:
            raise ValueError("something's not right")

        for child in parent_elm.iterchildren():
            if isinstance(child, CT_P):
                yield Paragraph(child, parent)
            elif isinstance(child, CT_Tbl):
                yield Table(child, parent)

    # 根据关键字返回所在段落
    def findTableWithKey(self, document, key):
        keepSearch = True
        para = None
        for block in self.iter_block_items(document):
            if isinstance(block, Paragraph):
                if block.text.find(key) >= 0:
                    keepSearch = False
                    para = block
            elif isinstance(block, Table):
                if not keepSearch:
                    return block, para
            else:
                raise ValueError("something's not right")
        return None, None

    def MergecellsChr(self, data, table, choose):
        for c in range(len(table.columns)):
            rcode = 0  # 记录重复的起始行
            k = table.columns[c].cells[0].text.replace('\n', '')
            for i, course in enumerate(data):
                if k in course.dictData:
                    if k == choose:
                        if i + 1 < len(data):
                            if len(data[i].dictData[k]) > 0:
                                tmp1 = copy.deepcopy(data[i].dictData[k].replace('\n', ''))
                                # print('赋值tmp1', tmp1, i, data[i + 1].dictData[k], c)
                            if len(data[i + 1].dictData[k]) == 0 and (i < len(data) - 2):
                                # print('记录清空：坐标，值', i + 1, c, data[i].dictData[k], i, c)
                                if rcode == 0:
                                    rcode += i + 1
                            elif rcode != 0:
                                if i == len(data) - 2:
                                    print(rcode, c, i + 3, c, '表格合并')
                                    for s in range(rcode, i + 2):
                                        table.columns[c].cells[s].text = ''
                                    table.cell(rcode, c).merge(table.cell(i + 2, c))
                                    table.columns[c].cells[rcode].text = ''
                                    self.insertCell(table.cell(rcode, c), tmp1, -1 == k.find('课程名称'))
                                    continue
                                # print(rcode, c, i + 1, c, '表格合并')
                                for s in range(rcode, i + 2):
                                    table.columns[c].cells[s].text = ''
                                table.cell(rcode, c).merge(table.cell(i + 1, c))
                                # print('tmp1:,长度',tmp1,len(tmp1))
                                table.columns[c].cells[rcode].text = ''
                                self.insertCell(table.cell(rcode, c), tmp1, -1 == k.find('课程名称'))
                                rcode -= rcode

    def MergecellsDig(self, data, table, choose):
        for c in range(len(table.columns)):
            rcode = 0  # 记录重复的起始行
            k = table.columns[c].cells[0].text.replace('\n', '')
            tmp1 = 0
            for i, course in enumerate(data):
                if k in course.dictData:
                    if k == choose:
                        if i + 1 < len(data):
                            if data[i].dictData[k] == data[i + 1].dictData[k] and (i < len(data) - 2):
                                if rcode == 0:
                                    tmp1 += data[i].dictData[k]
                                    rcode += i + 1
                            elif rcode != 0:
                                if i == len(data) - 2:
                                    print(rcode, c, i + 2, c, '表格合并')
                                    for s in range(rcode, i + 2):
                                        table.columns[c].cells[s].text = ''
                                    table.cell(rcode, c).merge(table.cell(i + 2, c))
                                    table.columns[c].cells[rcode].text = ''
                                    self.insertCell(table.cell(rcode, c), tmp1, -1 == k.find('课程名称'))
                                    continue
                                print(rcode, c, i + 1, c, '表格合并')
                                for s in range(rcode, i + 2):
                                    table.columns[c].cells[s].text = ''
                                table.cell(rcode, c).merge(table.cell(i + 1, c))
                                table.columns[c].cells[rcode].text = ''
                                self.insertCell(table.cell(rcode, c), tmp1, -1 == k.find('课程名称'))
                                for s in range(rcode, i):
                                    table.columns[c].cells[s].paragraphs[
                                        0].paragraph_format.alignment = WD_TABLE_ALIGNMENT.CENTER
                                tmp1 -= tmp1
                                rcode -= rcode

    # 一、培养目标
    # def writeTarget(self):
    #     self.replacePara("[[目标]]", "目标.txt")
    #     self.replacePara("[[预期]]", "预期.txt")
    #     self.replacePara("[[要求]]", "要求.txt")
    #     self.replacePara("[[主干学科]]", "主干学科.txt")
    #     self.replacePara("[[专业核心课程]]", "专业核心课程.txt")
    #     self.replacePara("[[主要实践性教学环节]]", "主要实践性教学环节.txt")
    def writeTarget(self): # 数据库用到
        self.replacePara("[[目标]]", "目标")
        self.replacePara("[[预期]]", "预期")
        self.replacePara("[[要求]]", "要求")
        self.replacePara("[[主干学科]]", "主干学科")
        self.replacePara("[[专业核心课程]]", "专业核心课程")
        self.replacePara("[[主要实践性教学环节]]", "主要实践性教学环节")

    # 三、毕业要求对培养目标的支撑矩阵
    def writeTargetMatrix(self, name):
        with open(self.rootPath + name, 'r', encoding='utf-8') as f:
            return f.read()

    # 四、主干学科
    def writeMainCourse(self, name):
        with open(self.rootPath + name, 'r', encoding='utf-8') as f:
            return f.read()


    def findKeyInPara(self, doc, key):
        target = None
        for paragraph in doc.paragraphs:
            paragraph_text = paragraph.text
            if paragraph_text.endswith(key):
                return paragraph

    # def replacePara(self, key, filename):
    #     target = None
    #     for paragraph in self.doc.paragraphs:
    #         paragraph_text = paragraph.text
    #         if paragraph_text.endswith(key):
    #             target = paragraph
    #
    #
    #             with open(self.rootPath + filename, 'r', encoding='utf-8') as f:
    #                 for eachline in f:
    #                     eachline = eachline.strip('\n')
    #                     run = target.insert_paragraph_before(eachline, "TEXT")
    #
    #             self.delete_paragraph(target)
    #
    #             break
    #
    #     return target

    def replacePara(self, key, name):
        wd = DbUse()
        wd.initdata()
        userdictinfo = wd.selectuserdictinfo(self.userid)
        major = userdictinfo["major"]
        target = None
        for paragraph in self.doc.paragraphs:
            paragraph_text = paragraph.text
            if paragraph_text.endswith(key):
                target = paragraph
                templatesdictinfo = wd.selecttemplatesdictinfo(major)
                if templatesdictinfo == -1:
                    f = ''
                else:
                    f = templatesdictinfo[name]
                f = f.split("\n")
                for eachline in f:
                    eachline = re.sub('[\n\r\']', '', eachline)
                    print(eachline)
                    run = target.insert_paragraph_before(eachline, "TEXT")
                self.delete_paragraph(target)
                break

        return target

    # 八、课程体系与毕业要求的对应关系矩阵
    def writeCourseMatrixSimple(self):
        table, para = self.findTableWithKey(self.doc, "[[" + "课程矩阵" + "]]")
        if not table:
            print("Error: writeCoueseTables:", "课程矩阵")
            return False

        for name in ["通识课", "学科基础课", "学科专业基础课", "集中性实践"]:
            courseGroup = self.courseDict[name]
            for course in courseGroup.courses:
                if len(course.gradReq.major) < 1:
                    continue

                row_new = table.add_row()  # 为表格最底部增加一行
                r = len(table.rows)
                row_last = table.rows[-1]
                self.insertCell(table.cell(r - 1, 0), course.name, False)
                for i in range(len(course.gradReq.major)):
                    w = course.gradReq.weight[i]
                    w_old = table.cell(r - 1, course.gradReq.major[i]).text
                    if len(w_old) > 0 and (w_old == 'M' and w == 'H'):
                        continue
                    self.insertCell(table.cell(r - 1, course.gradReq.major[i]), w)

        self.delete_paragraph(para)

    # 九、课程结构及学时学分统计
    def writeGradeSummit(self, year, major):
        document = MailMerge(self.rootPath + 'temp.docx')
        document.merge(year=year)
        document.merge(major=major)

        document.merge(period_common_must_in=str(self.period_common_must-self.period_common_must_prac_out))
        document.merge(period_common_choose_in=str(self.period_common_choose))
        document.merge(period_common_must_prac_out=str(self.period_common_must_prac_out))
        document.merge(period_common=str(self.period_common_all))

        document.merge(period_basic_must_in=str(self.period_basic_must-self.period_basic_must_prac_out))
        document.merge(period_basic=str(self.period_basic_all))
        document.merge(period_basic_must_prac_out=str(self.period_basic_must_prac_out))

        document.merge(period_major_must_in=str(self.period_major_must-self.period_major_must_prac_out))
        document.merge(period_major_must_prac_out=str(self.period_major_must_prac_out))
        document.merge(period_major=str(self.period_major_all))

        document.merge(period_major_choose_in=str(self.period_major_choose-self.period_major_choose_prac_out))
        document.merge(period_major_choose_prac_out=str(self.period_major_choose_prac_out))

        document.merge(period_common_p=format('%.2f' % (100 * self.period_common_p)))
        document.merge(period_basic_p=format('%.2f' % (100 * self.period_basic_p)))
        document.merge(period_major_p=format('%.2f' % (100 * self.period_major_p)))

        document.merge(credit_common_must=str(self.credit_common_must))
        document.merge(credit_common_choose=str(self.credit_common_choose))
        document.merge(credit_common=str(self.credit_common_all))
        document.merge(credit_basic_must=str(self.credit_basic_must))
        document.merge(credit_basic=str(self.credit_basic_all))
        document.merge(credit_major_must=str(self.credit_major_must))
        document.merge(credit_major_choose=str(self.credit_major_choose))
        document.merge(credit_major=str(self.credit_major_all))
        document.merge(credit_common_p=format('%.2f' % (100 * self.credit_common_p)))
        document.merge(credit_basic_p=format('%.2f' % (100 * self.credit_basic_p)))
        document.merge(credit_major_p=format('%.2f' % (100 * self.credit_major_p)))

        document.merge(credit_all=str(self.credit_all))
        document.merge(period_all=str(self.period_all))

        # summit
        document.merge(credit_major_choose=format('%.1f' % (self.credit_major_choose)))
        document.merge(credit_common_choose=format('%.1f' % (self.credit_common_choose)))
        document.merge(credit_major_not_choose=format('%.1f' % (self.credit_major_not_choose)))
        document.merge(credit_choose_p=format('%.2f' % (100 * self.credit_choose_p)))
        document.merge(period_prac=format('%.0f' % (self.period_prac)))
        document.merge(period_prac_p=format('%.2f' % (100 * self.period_prac_p)))

        # 实践学分
        document.merge(period_common_prac_in=str(self.period_common_prac_in))
        document.merge(period_common_prac_out=str(self.period_common_prac_out))
        document.merge(credit_common_prac_in=str(self.credit_common_prac_in))
        document.merge(credit_common_prac_out=str(self.credit_common_prac_out))
        document.merge(period_major_prac_in=str(self.period_major_prac_in))
        document.merge(period_major_prac_out=str(self.period_major_prac_out))
        document.merge(credit_major_prac_in=str(self.credit_major_prac_in))
        document.merge(credit_major_prac_out=str(self.credit_common_prac_out))

        document.merge(center_week_all=str(self.center_week_all))

        word_path = self.rootPath+str(self.userid)+"_"+str(self.word_y)+"mid.docx"

        document.write(word_path)

    # 十、课程设置及教学进程表
    def writeCoueseTables(self, keys):
        img=1
        word_path = self.rootPath + str(self.userid) + "_" + str(self.word_y) + "mid.docx"
        self.doc = Document(word_path)
        for key in keys:
            courses = self.courseDict[key]
            data = courses.courses

            table, para = self.findTableWithKey(self.doc, "[[" + key + "]]")
            if not table:
                print("Error: writeCoueseTables:", key)
                return False

            for course in data:
                row_before = len(table.rows)
                row_last = table.rows[row_before - 1]
                row_new = table.add_row()  # 为表格最底部增加一行
                row_last._tr.addprevious(row_new._tr)
                serial='序号'
                for c in range(len(table.columns)):
                    k = table.columns[c].cells[0].text.replace('\n', '')
                    if k in course.dictData:
                        text = course.dictData[k]
                        text = "0" if text == 0 else text
                        self.insertCell(table.cell(row_before - 1, c), text, -1 == k.find('课程名称'))
                    if k == serial:
                        text=img
                        self.insertCell(table.cell(row_before - 1, c), text, -1 == k.find('课程名称'))
                        img+=1

            if key == '专业选修课':
                self.MergecellsChr(data, table, '备注')

            # if key =='学科专业基础课':
            #     self.MergecellsDig(data, table,'开课学期')
            self.MergecellsDig(data, table, '开课学期')
            self.delete_paragraph(para)

        self.doc.save(word_path)


    def LoadCourse(self):   #读取exle文件
        # wb = xw.Book(self.rootPath + 'course.xlsx')
        # 加载已存在的工作薄
        wb = load_workbook(self.rootPath + 'course.xlsx')
        # 枚举工作薄所有工作表
        for ipage in range(len(wb.worksheets)):
            # 工作表  通识课......
            sheet = wb.worksheets[ipage]
            # row, col = sheet.used_range.shape
            # 将表中每一行的数据都读取到data_cource中  列表套列表
            data_course = []
            # 将除第一行（表头）都放到data_course中
            for i, data in enumerate(sheet.values):
                if i > 0:
                    li = []
                    for cell in data:
                        # 判断 cell是否为number
                        if isinstance(cell, int):
                            li.append(float(cell))
                            continue
                        li.append(cell)
                    data_course.append(li)
            # data_course = sheet.range((2, 1), (row, col)).value
            # 声明一个课程类的对象   并将课程名传过去  如：通识课......
            courses = Courses(sheet.title)
            self.courseDict[sheet.title] = courses
            courseInfo = list(sheet.values)[0]
            # 初始化Courses类里面的titleDict列表  将每一个的参数（如课程序号）赋值为一个数字
            courses.initCoursesInfo(courseInfo)

            if len(data_course) > 0:
                if (isinstance(data_course[0], list) and len(data_course[0]) > 0):
                    for r in data_course:
                        courses.addCourse(r)
                else:
                    courses.addCourse(data_course)
        self.updateSummit()

    # def LoadCousersFromDB(self, userid):
    #     wd = DbUse()
    #     wd.initdata()
    #     userdictinfo = wd.selectuserdictinfo(userid)
    #     self.userid = userid
    #     cour = wd.selectmajorcourses(userdictinfo["major"])
    #     recode = [8, 8, 7, 7, 7, 3, 3]
    #     for ipage in range(len(wd.stname)):
    #         sheet = cour[ipage]
    #         # row, col = sheet.used_range.shape
    #         data_course = []
    #         for i, data in enumerate(sheet):
    #             li = []
    #             for p, cell in enumerate(data):
    #                 # 判断 cell是否为number
    #                 if p in range(4, recode[ipage] + 3):
    #                     # print(p)
    #                     if isinstance(cell, int) or cell == None:
    #                         if cell == None:
    #                             cell = 0
    #                         li.append(float(cell))
    #                         continue
    #                 li.append(cell)
    #             data_course.append(li)
    #         courses = Courses(wd.stname[ipage])
    #         self.courseDict[wd.stname[ipage]] = courses
    #         courseInfo = wd.coursestileyuan[ipage]
    #         courses.initCoursesInfo(courseInfo)
    #         if len(data_course) > 0:
    #             if (isinstance(data_course[0], list) and len(data_course[0]) > 0):
    #                 for r in data_course:
    #                     courses.addCourse(r)
    #             else:
    #                 courses.addCourse(data_course)
    #     self.updateSummit()

    def LoadCousersFromDB(self, userid,year):
        wd = DbUse()
        wd.initdata()
        wd.userid=userid
        self.userid = userid
        coursetemplatesdictinfo = wd.selectcoursetemplates(year)
        wb = load_workbook(coursetemplatesdictinfo.get('coursepath'))
        recode = [8, 8, 7, 7, 7, 3, 3]
        for ipage in range(len(wb.worksheets)):
            sheet = wb.worksheets[ipage]
            # row, col = sheet.used_range.shape
            data_course = []
            for i, data in enumerate(sheet.values):
                # print(data,"data")
                if i > 0:
                    li = []
                    for p, cell in enumerate(data):
                        # 判断 cell是否为number
                        if p in range(3, recode[ipage] + 3):
                            if isinstance(cell, int) or cell == None:
                                if cell == None:
                                    cell = 0
                                li.append(float(cell))
                                continue
                        li.append(cell)
                    data_course.append(li)
            print(data_course)
            for i in data_course:
                print(i)
            # data_course = sheet.range((2, 1), (row, col)).value
            courses = Courses(sheet.title)
            self.courseDict[sheet.title] = courses
            courseInfo = list(sheet.values)[0]

            courses.initCoursesInfo(courseInfo)

            if len(data_course) > 0:
                if (isinstance(data_course[0], list) and len(data_course[0]) > 0):
                    for r in data_course:
                        courses.addCourse(r)
                else:
                    courses.addCourse(data_course)
        self.updateSummit()

    def analyse_xlse(self,path,userid,year):
        wd = DbUse()
        wd.initdata()
        wd.userid = userid
        self.userid = userid
        coursetemplatesdictinfo = wd.selectcoursetemplates(year)
        wb = load_workbook(path)
        recode = [8, 8, 7, 7, 7, 3, 3]
        for ipage in range(len(wb.worksheets)):
            sheet = wb.worksheets[ipage]
            # row, col = sheet.used_range.shape
            data_course = []
            for i, data in enumerate(sheet.values):
                # print(data,"data")
                if i > 0:
                    li = []
                    for p, cell in enumerate(data):
                        # 判断 cell是否为number
                        if p in range(3, recode[ipage] + 3):
                            if isinstance(cell, int) or cell == None:
                                if cell == None:
                                    cell = 0
                                li.append(float(cell))
                                continue
                        li.append(cell)
                    data_course.append(li)
            print(data_course)
            for i in data_course:
                print(i)
            # data_course = sheet.range((2, 1), (row, col)).value
            courses = Courses(sheet.title)
            self.courseDict[sheet.title] = courses
            courseInfo = list(sheet.values)[0]

            courses.initCoursesInfo(courseInfo)

            if len(data_course) > 0:
                if (isinstance(data_course[0], list) and len(data_course[0]) > 0):
                    for r in data_course:
                        courses.addCourse(r)
                else:
                    courses.addCourse(data_course)
        self.updateSummit()


    def writeWord(self):

        name = self.rootPath+str(self.userid)+"_"+str(self.word_y)+"mid.docx"
        self.doc.save(name)
        print("Saving done: " + name)

    def analyse_exl(self, userid,year):
        wd = DbUse()
        wd.initdata()
        wd.userid = userid
        mainid = wd.selectmainidpassuserid(userid)
        coursetemplatesdictinfo = wd.selectcoursetemplates(year)
        wb = load_workbook(coursetemplatesdictinfo.get('coursepath'))
        for ipage in range(len(wb.worksheets)):
            sheet = wb.worksheets[ipage]
            if ipage == 0:  # 通识课
                for i, data in enumerate(sheet.values):  # 枚举通识课下标和每一行数据
                    if i == 0:
                        continue
                    data = list(data)  # 将元组转为列表
                    for j in range(1, len(data) + 1):  # 枚举一行数据中的每一个
                        if j == 1:  # 课程序号
                            if data[j - 1] == None:  # 如果为空就挨个赋值
                                data[j - 1] = i
                            else:  # 不是空就强制转换为数据库中特定类型
                                data[j - 1] = int(data[j - 1])
                        elif j >= 2 and j <= 3:  # 课程代码 课程名称
                            if data[j - 1] == None:
                                data[j - 1] = "None"
                            else:
                                data[j - 1] = str(data[j - 1])
                        elif j >= 4 and j <= 11:  # 学分学时等
                            if data[j - 1] == None:
                                data[j - 1] = float(0)
                            else:
                                data[j - 1] = float(data[j - 1])
                        else:
                            if data[j - 1] == None:
                                data[j - 1] = "None"
                            else:
                                data[j - 1] = str(data[j - 1])
                    wd.insertcourse1(data,mainid)

            elif ipage == 1:  # 通识选修课
                for i, data in enumerate(sheet.values):  # 枚举通识课下标和每一行数据
                    if i == 0:
                        continue
                    data = list(data)  # 将元组转为列表
                    for j in range(1, len(data) + 1):  # 枚举一行数据中的每一个
                        if j == 1:  # 课程序号
                            if data[j - 1] == None:  # 如果为空就挨个赋值
                                data[j - 1] = i
                            else:  # 不是空就强制转换为数据库中特定类型
                                data[j - 1] = int(data[j - 1])
                        elif j >= 2 and j <= 3:  # 课程代码 课程名称
                            if data[j - 1] == None:
                                data[j - 1] = "None"
                            else:
                                data[j - 1] = str(data[j - 1])
                        elif j >= 4 and j <= 11:  # 学分学时等
                            if data[j - 1] == None:
                                data[j - 1] = float(0)
                            else:
                                data[j - 1] = float(data[j - 1])
                        else:
                            if data[j - 1] == None:
                                data[j - 1] = "None"
                            else:
                                data[j - 1] = str(data[j - 1])
                    wd.insertcourse2(data,mainid)

            elif ipage == 2:  # 学科基础课
                for i, data in enumerate(sheet.values):  # 枚举通识课下标和每一行数据
                    if i == 0:
                        continue
                    data = list(data)  # 将元组转为列表
                    for j in range(1, len(data) + 1):  # 枚举一行数据中的每一个
                        if j == 1:  # 课程序号
                            if data[j - 1] == None:  # 如果为空就挨个赋值
                                data[j - 1] = i
                            else:  # 不是空就强制转换为数据库中特定类型
                                data[j - 1] = int(data[j - 1])
                        elif j >= 2 and j <= 3:  # 课程代码 课程名称
                            if data[j - 1] == None:
                                data[j - 1] = "None"
                            else:
                                data[j - 1] = str(data[j - 1])
                        elif j >= 4 and j <= 10:  # 学分学时等
                            if data[j - 1] == None:
                                data[j - 1] = float(0)
                            else:
                                data[j - 1] = float(data[j - 1])
                        else:
                            if data[j - 1] == None:
                                data[j - 1] = "None"
                            else:
                                data[j - 1] = str(data[j - 1])
                    wd.insertcourse3(data,mainid)

            elif ipage == 3:  # 学科专业基础课
                for i, data in enumerate(sheet.values):  # 枚举通识课下标和每一行数据
                    if i == 0:
                        continue
                    data = list(data)  # 将元组转为列表
                    for j in range(1, len(data) + 1):  # 枚举一行数据中的每一个
                        if j == 1:  # 课程序号
                            if data[j - 1] == None:  # 如果为空就挨个赋值
                                data[j - 1] = i
                            else:  # 不是空就强制转换为数据库中特定类型
                                data[j - 1] = int(data[j - 1])
                        elif j >= 2 and j <= 3:  # 课程代码 课程名称
                            if data[j - 1] == None:
                                data[j - 1] = "None"
                            else:
                                data[j - 1] = str(data[j - 1])
                        elif j >= 4 and j <= 10:  # 学分学时等
                            if data[j - 1] == None:
                                data[j - 1] = float(0)
                            else:
                                data[j - 1] = float(data[j - 1])
                        else:
                            if data[j - 1] == None:
                                data[j - 1] = "None"
                            else:
                                data[j - 1] = str(data[j - 1])
                    wd.insertcourse4(data,mainid)

            elif ipage == 4:  # 专业选修课
                for i, data in enumerate(sheet.values):  # 枚举通识课下标和每一行数据
                    if i == 0:
                        continue
                    data = list(data)  # 将元组转为列表
                    for j in range(1, len(data) + 1):  # 枚举一行数据中的每一个
                        if j == 1:  # 课程序号
                            if data[j - 1] == None:  # 如果为空就挨个赋值
                                data[j - 1] = i
                            else:  # 不是空就强制转换为数据库中特定类型
                                data[j - 1] = int(data[j - 1])
                        elif j >= 2 and j <= 3:  # 课程代码 课程名称
                            if data[j - 1] == None:
                                data[j - 1] = "None"
                            else:
                                data[j - 1] = str(data[j - 1])
                        elif j >= 4 and j <= 10:  # 学分学时等
                            if data[j - 1] == None:
                                data[j - 1] = float(0)
                            else:
                                data[j - 1] = float(data[j - 1])
                        else:
                            if data[j - 1] == None:
                                data[j - 1] = "None"
                            else:
                                data[j - 1] = str(data[j - 1])
                    wd.insertcourse5(data,mainid)

            elif ipage == 5:  # 集中性实践
                for i, data in enumerate(sheet.values):  # 枚举通识课下标和每一行数据
                    if i == 0:
                        continue
                    data = list(data)  # 将元组转为列表
                    for j in range(1, len(data) + 1):  # 枚举一行数据中的每一个
                        if j == 1:  # 课程序号
                            if data[j - 1] == None:  # 如果为空就挨个赋值
                                data[j - 1] = i
                            else:  # 不是空就强制转换为数据库中特定类型
                                data[j - 1] = int(data[j - 1])
                        elif j >= 2 and j <= 3:  # 课程代码 课程名称
                            if data[j - 1] == None:
                                data[j - 1] = "None"
                            else:
                                data[j - 1] = str(data[j - 1])
                        elif j >= 4 and j <= 6:  # 学分学时等
                            if data[j - 1] == None:
                                data[j - 1] = float(0)
                            else:
                                data[j - 1] = float(data[j - 1])
                        else:
                            if data[j - 1] == None:
                                data[j - 1] = "None"
                            else:
                                data[j - 1] = str(data[j - 1])
                    wd.insertcourse6(data,mainid)
            else:  # 第二课堂
                for i, data in enumerate(sheet.values):  # 枚举通识课下标和每一行数据
                    if i == 0:
                        continue
                    data = list(data)  # 将元组转为列表
                    for j in range(1, len(data) + 1):  # 枚举一行数据中的每一个
                        if j == 1:  # 课程序号
                            if data[j - 1] == None:  # 如果为空就挨个赋值
                                data[j - 1] = i
                            else:  # 不是空就强制转换为数据库中特定类型
                                data[j - 1] = int(data[j - 1])
                        elif j >= 2 and j <= 3:  # 课程代码 课程名称
                            if data[j - 1] == None:
                                data[j - 1] = "None"
                            else:
                                data[j - 1] = str(data[j - 1])
                        elif j >= 4 and j <= 6:  # 学分学时等
                            if data[j - 1] == None:
                                data[j - 1] = float(0)
                            else:
                                data[j - 1] = float(data[j - 1])
                        else:
                            if data[j - 1] == None:
                                data[j - 1] = "None"
                            else:
                                data[j - 1] = str(data[j - 1])
                    wd.insertcourse7(data, mainid)

if __name__ == '__main__':
    peiyang = PeiYang('template/湘南学院/计算机科学与技术/')

    peiyang.LoadCourse()

    # peiyang.Semester_course()

    peiyang.showSummit()

    peiyang.writeGradeSummit('2022', '计算机科学与技术')



    peiyang.writeCoueseTables(["通识课", "学科基础课", "学科专业基础课", "专业选修课", "集中性实践"])

    peiyang.writeCourseMatrixSimple()

    # 本地调试不要调用这个。这个要连接数据库
    #peiyang.writeTarget()
    peiyang.writeWord()
